# coding:utf-8
import ConfigParser, MySQLdb, hashlib
from datetime import *
from operator import itemgetter

def myencode(string):
    string = string + string[0] + string[-1]
    return hashlib.md5(string).hexdigest()


class Database(object):
    def __init__(self):
        conf = ConfigParser.ConfigParser()
        conf.read('default.cfg')
        name = conf.get('db', 'NAME')
        passwd = conf.get('db', 'PASSWORD')
        self.con = MySQLdb.connect('localhost', name, passwd, 'cis', charset='utf8')

    def get_classes(self, uid, semester):
        with self.con:
            cur = self.con.cursor()
            sql = 'SELECT * FROM class WHERE teacher = %s AND semester = %s ORDER BY class_id'
            param = (uid, semester)
            cur.execute(sql, param)
            result = cur.fetchall()
            return result

    def get_all_classes(self, uid):
        with self.con:
            cur = self.con.cursor()
            sql = 'SELECT * FROM class WHERE teacher = %s ORDER BY class_id'
            param = (uid,)
            cur.execute(sql, param)
            result = cur.fetchall()
            return result

    def get_student_classes(self, name, semester):
        with self.con:
            cur = self.con.cursor()
            sql = 'SELECT * FROM student_class WHERE user = %s'
            param = (name,)
            cur.execute(sql, param)
            classes = cur.fetchall()
            result = []
            for x in classes:
                sql = 'SELECT * from class WHERE class_id = %s AND semester = %s'
                param = (x[1], semester)
                cur.execute(sql, param)
                x = cur.fetchone()
                if x:
                    result.append(x)
            if len(result) == 0:
                return None
            else:
                return result

    def log_in(self, name, passwd):
        with self.con:
            if len(passwd) == 0:
                return -1
            else:
                cur = self.con.cursor()
                sql = 'SELECT * FROM user WHERE BINARY username = %s AND BINARY password = %s'
                param = (name, myencode(passwd))
                cur.execute(sql, param)
                result = cur.fetchone()
                if result == None:
                    return 0
                else:
                    self.role = result[3]
                    self.uid = result[0]
                    self.name = result[1]
                    if self.role == 'stu':
                        self.realname = self.student_realname(self.name)
                    elif self.role == 'thr':
                        self.realname = self.teacher_realname(self.uid)
                    return 1

    def student_realname(self, name):
        with self.con:
            cur = self.con.cursor()
            sql = 'SELECT name FROM student WHERE username = %s'
            param = (name,)
            cur.execute(sql, param)
            result = cur.fetchone()[0]
            return result

    def teacher_realname(self, uid):
        with self.con:
            cur = self.con.cursor()
            sql = 'SELECT name FROM teacher WHERE user_id = %s'
            param = (uid,)
            cur.execute(sql, param)
            name = cur.fetchone()[0]
            return name

    def show_course(self):
        with self.con:
            cur = self.con.cursor()
            sql = 'SELECT * FROM course ORDER BY course_id DESC'
            cur.execute(sql)
            result = cur.fetchall()
            return result

    def get_course_name(self, cid):
        with self.con:
            cur = self.con.cursor()
            sql = 'SELECT * FROM course WHERE course_id = %s'
            param = (cid,)
            cur.execute(sql, param)
            name = cur.fetchone()[1]
            return name

    def add_course(self, form):
        with self.con:
            cur = self.con.cursor()
            sql = 'INSERT INTO course (title, detail, cdate) values (%s, %s, NOW())'
            param = (form['title'], form['detail'])
            cur.execute(sql, param)
            result = cur.fetchone()
            return result == None

    def del_course(self, cid):
        with self.con:
            cur = self.con.cursor()
            sql = 'DELETE FROM course WHERE course_id = %s';
            param = (cid,)
            cur.execute(sql, param)
            result = cur.fetchone()
            return result == None

    def add_teacher(self, username, name):
        with self.con:
            cur = self.con.cursor()
            sql = 'INSERT INTO user (username, password, role, cdate) values (%s, %s, %s, NOW())'
            param = (username, myencode(username), 'thr')
            cur.execute(sql, param)
            result = cur.fetchone()
            if result == None:
                sql = 'SELECT user_id from user WHERE username = %s'
                param = (username,)
                cur.execute(sql, param)
                user_id = cur.fetchone()
                sql = 'INSERT INTO teacher (user_id, name) values (%s, %s)'
                param = (user_id, name)
                cur.execute(sql, param)
                result = cur.fetchone()
                if result == None:
                    return 1
            return 0
            
 

    def get_teachers(self):
        with self.con:
            cur = self.con.cursor()
            sql = 'SELECT * FROM teacher'
            cur.execute(sql)
            result = cur.fetchall()
            return result

    def get_teacher(self, uid):
        with self.con:
            cur = self.con.cursor()
            sql = 'SELECT * FROM teacher WHERE user_id = %s'
            param = (uid,)
            cur.execute(sql, param)
            result = cur.fetchone()
            return result[1]

    def get_teacher_by_name(self, name):
        with self.con:
            cur = self.con.cursor()
            sql = 'SELECT * FROM user WHERE username = %s'
            param = (name,)
            cur.execute(sql, param)
            result = cur.fetchone()
            return result[0]

    def show_class(self, cid, num2):
        with self.con:
            cur = self.con.cursor()
            sql = 'SELECT * FROM class WHERE course = %s ORDER BY class_id DESC LIMIT %s, 4'
            param = (cid, num2)
            cur.execute(sql, param)
            classes = cur.fetchall()
            result = []
            for _class in classes:
                result.append([_class[0], _class[1], _class[2], _class[6], self.get_teacher(_class[5])])
            sql = 'SELECT COUNT(*) FROM class WHERE course = %s'
            param = (cid,)
            cur.execute(sql, param)
            total = cur.fetchone()[0]
            return (result, total)

    def add_class(self, num, form):
        with self.con:
            cur = self.con.cursor()
            sql = 'INSERT INTO class (title, detail, cdate, course, teacher, semester) values (%s, %s, NOW(), %s, %s, %s)'
            param = (form['title'], form['detail'], num, form['teacher'], form['semester'])
            cur.execute(sql, param)
            result = cur.fetchone()
            if result == None:
                sql = 'SELECT * FROM class ORDER BY class_id DESC LIMIT 0,1'
                cur.execute(sql)
                class_id = cur.fetchone()[0]
            return (result == None, class_id)

    def del_class(self, num):
        with self.con:
            cur = self.con.cursor()
            sql = 'DELETE FROM class WHERE class_id = %s'
            param = (num,)
            cur.execute(sql, param)
            result = cur.fetchone()
            return result == None

    def new_notice(self):
        with self.con:
            cur = self.con.cursor()
            sql = 'SELECT * FROM notice WHERE class_id=0 ORDER BY notice DESC LIMIT 0,1'
            cur.execute(sql)
	    notice = cur.fetchone()
            if notice == None:
		notice = []
            else:
            	notice = list(notice)
		notice[3] = notice[3].strftime("%Y-%m-%d")
            return notice

    def show_notice(self, num2, class_id):
        with self.con:
            cur = self.con.cursor()
            sql1 = 'SELECT * FROM notice WHERE class_id = %s ORDER BY notice DESC LIMIT %s,4'
            sql2 = 'SELECT COUNT(*) FROM notice WHERE class_id = %s'
            param = (class_id, num2)
            cur.execute(sql1, param)
            result = cur.fetchall()
            param = (class_id,)
            cur.execute(sql2, param)
            total = cur.fetchone()[0]
            return (result, total)

    def add_notice(self, form, class_id):
        with self.con:
            cur = self.con.cursor()
            sql = 'INSERT INTO notice (title, detail, cdate, class_id) values (%s, %s, NOW(), %s)'
            param = (form['title'], form['detail'], class_id)
            cur.execute(sql, param)
            result = cur.fetchone()
            return result == None

    def del_notice(self, notice):
        with self.con:
            cur = self.con.cursor()
            sql = 'DELETE FROM notice WHERE notice = %s'
            param = (notice,)
            cur.execute(sql, param)
            result = cur.fetchone()
            return result == None

    def add_user(self, form):
        with self.con:
            cur = self.con.cursor()
            sql = 'INSERT INTO user (username, password, role) values (%s, %s, %s)'
            param = (form['name'], myencode(form['name']), 'thr')
            cur.execute(sql, param)
            result = cur.fetchone()
            if not result:
                user_id = self.get_teacher_by_name(form['name'])
                sql = 'INSERT INTO teacher (user_id, name) values (%s, %s)'
                param = (user_id, form['real_name'])
                cur.execute(sql, param)
                result = cur.fetchone()
            return result == None

    def reset_password(self, name):
        with self.con:
            cur = self.con.cursor()
            sql = 'UPDATE user SET password = %s WHERE username = %s'
            param = (myencode(name), name)
            cur.execute(sql, param)
            result = cur.fetchone()
            return result == None

    def change_password(self, user_id, password):
        with self.con:
            cur = self.con.cursor()
            sql = 'UPDATE user SET password = %s WHERE user_id = %s'
            param = (myencode(password), user_id)
            cur.execute(sql, param)
            result = cur.fetchone()
            return result == None

    def show_homework(self, class_id):
        with self.con:
            cur = self.con.cursor()
            sql =' SELECT * from homework WHERE class = %s'
            param = (class_id,)
            cur.execute(sql, param)
            results = cur.fetchall()
            homeworks = []
            for result in results:
                sql = 'SELECT COUNT(*) FROM work WHERE homework_id = %s'
                param = (result[0],)
                cur.execute(sql, param)
                num = cur.fetchone()[0]
                homework = (result[1], result[4], result[5], result[0], num)
                homeworks.append(homework)
            sql = 'SELECT COUNT(*) FROM student_class WHERE class = %s'
            param = (class_id,)
            cur.execute(sql, param)
            total = cur.fetchone()[0]
            return (homeworks, total)

    def show_homework_detail(self, homework_id):
        with self.con:
            cur = self.con.cursor()
            sql = 'SELECT * FROM homework WHERE homework_id = %s'
            param = (homework_id,)
            cur.execute(sql, param)
            homework = cur.fetchone()
            return homework

    def change_homework_detail(self, form, homework_id):
        with self.con:
            cur = self.con.cursor()
            sql = 'UPDATE homework SET title=%s, content=%s, demand=%s, deadline=%s WHERE homework_id=%s' 
            param = (form['title'], form['content'], form['demand'], form['deadline'], homework_id)
            cur.execute(sql, param)
            return cur.fetchone() is None

    def del_homework(self, homework_id):
        with self.con:
            cur = self.con.cursor()
            sql1 = 'DELETE FROM homework WHERE homework_id = %s'
            sql2 = 'DELETE FROM work WHERE homework_id = %s'
            param = (homework_id,)
            cur.execute(sql1, param)
            if cur.fetchone() == None:
                cur.execute(sql2, param)
                if cur.fetchone() == None:
                    return 1
            return 0

    def show_student_homework(self, class_id, homework_id):
        with self.con:
            cur = self.con.cursor()
            sql = 'SELECT user FROM work WHERE homework_id = %s'
            param = (homework_id,)
            cur.execute(sql, param)
            results = cur.fetchall()
            done = []
            if results != None:
                for result in results:
                    sql = 'SELECT username FROM user WHERE user_id = %s'
                    param = (result[0],)
                    cur.execute(sql, param)
                    username = cur.fetchone()[0]
                    done.append(username)
            sql = 'SELECT user FROM student_class WHERE class = %s'
            param = (class_id,)
            cur.execute(sql, param)
            users = cur.fetchall()
            students = []
            for user in users:
                sql = 'SELECT * FROM student WHERE username = %s'
                param = (user[0],)
                cur.execute(sql, param)
                student = cur.fetchone()
                students.append(student)
            return (done, students)

    def show_student_done_homework(self, homework_id):
        with self.con:
            cur = self.con.cursor()
            sql = 'SELECT user,score FROM work  WHERE homework_id = %s'
            param = (homework_id,)
            cur.execute(sql, param)
            results = cur.fetchall()
            if results == None:
                return []
            students = []
            for result in results:
                sql = 'SELECT username FROM user WHERE user_id = %s'
                param = (result[0],)
                cur.execute(sql, param)
                username = cur.fetchone()[0]
                sql = 'SELECT * FROM student WHERE username = %s'
                param = (username,)
                cur.execute(sql, param)
                infos = cur.fetchone()
                student = list(infos)
                student.append(result[0])
                student.append(result[1])
                students.append(student)
            return sorted(students, key=itemgetter(0))

    def score(self, num, form):
        with self.con:
            try:
                cur = self.con.cursor()
                sql = 'UPDATE work SET score = %s WHERE homework_id = %s AND user = %s'
                for student in form:
                    if(form[student] != None):
                        param = (form[student], num, student)
                        cur.execute(sql, param) 
                return None
            except Exception, e:
                return e

    def show_score(self, num):
        with self.con:
            cur = self.con.cursor()
            sql = 'SELECT homework_id, title FROM homework WHERE class = %s ORDER BY homework_id'
            param = (num,)
            cur.execute(sql, param)
            homeworks = cur.fetchall()
            if not homeworks:
                return ([], [])
            sql = 'SELECT user FROM student_class WHERE class = %s'
            cur.execute(sql, param)
            users = cur.fetchall()
            if not users:
                return ([], [])
            students = []
            for user in users:
                sql = 'SELECT name FROM student WHERE username = %s'
                param = (user[0],)
                cur.execute(sql, param)
                username = cur.fetchone()[0]
                sql = 'SELECT user_id FROM user WHERE username = %s'
                cur.execute(sql, param)
                user_id = cur.fetchone()[0]
                student = [user[0], username]
                for homework in homeworks:
                    sql = 'SELECT score FROM work WHERE homework_id = %s AND user = %s'
                    param = (homework[0], user_id)
                    cur.execute(sql, param)
                    score_list = cur.fetchone()
                    if not score_list:
                        score = 0
                    else:
                        score = score_list[0]
                    if score == -1:
                        score = 0
                    student.append(score)
                students.append(student)
            return (students, homeworks)

    def show_score_excel(self, num):
        with self.con:
            cur = self.con.cursor()
            sql = 'SELECT homework_id, title FROM homework WHERE class = %s ORDER BY homework_id'
            param = (num,)
            cur.execute(sql, param)
            homeworks = cur.fetchall()
            if not homeworks:
                return ([], [])
            sql = 'SELECT user FROM student_class WHERE class = %s'
            cur.execute(sql, param)
            users = cur.fetchall()
            if not users:
                return ([], [])
            students = []
            for user in users:
                sql = 'SELECT * FROM student WHERE username = %s'
                param = (user[0],)
                cur.execute(sql, param)
                student = list(cur.fetchone())
                sql = 'SELECT user_id FROM user WHERE username = %s'
                cur.execute(sql, param)
                user_id = cur.fetchone()[0]
                for homework in homeworks:
                    sql = 'SELECT score FROM work WHERE homework_id = %s AND user = %s'
                    param = (homework[0], user_id)
                    cur.execute(sql, param)
                    score_list = cur.fetchone()
                    if not score_list:
                        score = 0
                    else:
                        score = score_list[0]
                    if score == -1:
                        score = 0
                    student.append(score)
                students.append(student)
            return (students, homeworks)

    def add_homework(self, form, num):
        with self.con:
            cur = self.con.cursor()
            sql = 'INSERT INTO homework (title, content, demand, cdate, deadline, class) values (%s, %s, %s, NOW(), %s, %s)'
            param = (form['title'], form['content'], form['demand'], form['deadline'], num)
            cur.execute(sql, param)
            result = cur.fetchone()
            cur.execute('SELECT LAST_INSERT_ID()')
            class_id = cur.fetchone()[0]
            return (result == None, class_id)

    def add_file(self, title, path, type, class_id, user_id):
        with self.con:
            cur = self.con.cursor()
            sql = 'INSERT INTO file (title, url, type, cdate, class, user) values (%s, %s, %s, NOW(), %s, %s)'
            param = (title, path, type, class_id, user_id)
            cur.execute(sql, param)
            result = cur.fetchone()
            return result == None

    def show_file(self, class_id, type):
        with self.con:
            if type == 'ppt':
                cur = self.con.cursor()
                sql = 'SELECT * FROM file WHERE class = %s AND type = %s'
                param = (class_id, 'ppt')
                cur.execute(sql, param)
                results = cur.fetchall()
                files = [(result[0], result[1], result[2].rsplit('/', 1)[-1], result[4].strftime('%Y-%m-%d')) for result
                         in results]
                return files

    def get_file_path(self, file_id):
        with self.con:
            cur = self.con.cursor()
            sql = 'SELECT url FROM file WHERE file_id = %s'
            param = (file_id,)
            cur.execute(sql, param)
            path = cur.fetchone()[0]
            return path

    def del_file(self, file_id):
        with self.con:
            cur = self.con.cursor()
            sql = 'DELETE FROM file WHERE file_id = %s '
            param = (file_id,)
            cur.execute(sql, param)
            result = cur.fetchone()
            return result == None

    def get_realname(self, uid):
        with self.con:
            cur = self.con.cursor()
            sql = 'SELECT * from user WHERE user_id = %s'
            param = (uid,)
            cur.execute(sql, param)
            result = cur.fetchone()
            role = result[3]
            if role == 'stu':
                sql = 'SELECT student.name FROM student WHERE username = %s'
                param = (result[1],)
                cur.execute(sql, param)
                return cur.fetchone()[0]
            else:
                sql = 'SELECT teacher.name FROM teacher WHERE user_id = %s'
                param = (uid,)
                cur.execute(sql, param)
                return cur.fetchone()[0]

    def show_discussion(self, now, class_id):
        with self.con:
            cur = self.con.cursor()
            sql = 'SELECT * FROM discussion WHERE class = %s AND pre_discuss = -1 ORDER BY cdate DESC LIMIT %s,4'
            param = (class_id, now)
            cur.execute(sql, param)
            results = cur.fetchall()
            sql = 'SELECT COUNT(*) FROM discussion WHERE class = %s'
            param = (class_id,)
            cur.execute(sql, param)
            total = cur.fetchone()[0]
            return (results, total)

    def show_discussion_detail(self, class_id):
        with self.con:
            details = []
            cur = self.con.cursor()
            sql = 'SELECT * from discussion WHERE discuss_id = %s'
            param = (class_id,)
            cur.execute(sql, param)
            result = cur.fetchone()
            while result:
                details.append(result)
                sql = 'SELECT * from discussion WHERE pre_discuss = %s'
                param = (result[0],)
                cur.execute(sql, param)
                result = cur.fetchone()
            return details
            


    def add_discussion(self, content, path, class_id, user_id, pre):
        with self.con:
            cur = self.con.cursor()
            sql = 'INSERT INTO discussion (class, user, cdate, picture, pre_discuss, content) VALUES (%s, %s, NOW(), %s, %s, %s)'
            param = (class_id, user_id, path, pre, content)
            cur.execute(sql, param)
            result = cur.fetchone()
            return result == None

    def del_discussion(self, discuss_id):
        with self.con:
            cur = self.con.cursor()
            sql = 'DELETE FROM discussion WHERE discuss_id = %s'
            param = (discuss_id,)
            cur.execute(sql, param)
            sql = 'SELECT * FROM discussion WHERE pre_discuss = %s'
            param = (discuss_id,)
            cur.execute(sql, param)
            result = cur.fetchone()
            while result:
                sql = 'DELETE FROM discussion WHERE discuss_id = %s'
                param = (result[0],)
                cur.execute(sql, param)
                sql = 'SELECT * FROM discussion WHERE pre_discuss = %s'
                cur.execute(sql, param)
                result = cur.fetchone()
            return cur.fetchone() == None

    def show_students(self, class_id):
        with self.con:
            cur = self.con.cursor()
            sql = 'SELECT * FROM student_class WHERE class = %s'
            param = (class_id,)
            cur.execute(sql, param)
            results = cur.fetchall()
            students = []
            for result in results:
                sql = 'SELECT * FROM student WHERE username = %s'
                param = (result[0],)
                cur.execute(sql, param)
                student = cur.fetchone()
                if student:
                    students.append(student)
            return students

    def change_student(self, info):
        with self.con:
            cur = self.con.cursor()
            sql = 'UPDATE student SET name = %s, college = %s, major = %s, class = %s WHERE username = %s'
            param = (info[1], info[4], info[3], info[2], info[0])
            cur.execute(sql, param)
            result = cur.fetchone()
            return result == None

    def add_user_student(self, info):
        with self.con:
            cur = self.con.cursor()
            sql = 'INSERT INTO student (username, name, class, major, college) VALUES (%s, %s, %s, %s, %s)'
            cur.execute(sql, info)
            result = cur.fetchone()
            if result:
                return 0
            sql = 'INSERT INTO user (username, password, role, cdate) values (%s, %s, %s, NOW())'
            param = (info[0], myencode(info[0]), 'stu')
            cur.execute(sql, param)
            result = cur.fetchone()
            return result == None

    def add_student(self, info, class_id):
        with self.con:
            try:
                cur = self.con.cursor()
                sql = 'SELECT * FROM user WHERE username = %s'
                param = (info[0],)
                cur.execute(sql, param)
                result = cur.fetchone()
                if result:
                    result = self.change_student(info)
                else:
                    result = self.add_user_student(info)
                if not result:
                    return 0
                sql = 'INSERT INTO student_class (class, user) VALUES (%s, %s)'
                param = (class_id, info[0])
                cur.execute(sql, param)
                result = cur.fetchone()
                return result == None
            except Exception, e:
                print e
                return 0
            
    def del_student(self, username):
        with self.con:
            try:
                cur = self.con.cursor()
                sql1 = 'SELECT * FROM student_class WHERE user = %s'
                param = (username,)
                cur.execute(sql1, param)
                result = cur.fetchone()
                if not result:
                    return 0
                sql2 = 'DELETE FROM student_class WHERE user = %s'
                cur.execute(sql2, param)
                return 1
            except:
                return 0

    def show_homeworks_student(self, class_id, user_id):
        with self.con:
            try:
                cur = self.con.cursor()
                sql = 'SELECT * from homework WHERE class = %s ORDER BY homework_id DESC'
                param = (class_id,)
                cur.execute(sql, param)
                results = cur.fetchall()
                homeworks = []
                for result in results:
                    sql = 'SELECT score from work WHERE homework_id = %s AND user = %s'
                    param = (result[0], user_id)
                    cur.execute(sql, param)
                    score_list = cur.fetchone()
                    if not score_list:
                        now = datetime.now()
                        if now >= result[5]:
                            score = u'未在截止时间前上传作业'
                        else:
                            score = u'暂未上传'
                    else:
                        score = score_list[0]
                        if score == -1:
                            score = u'暂未批改'
                    homework = (result[1], result[4].strftime('%Y-%m-%d'), result[5], score, result[0])
                    homeworks.append(homework)
                return homeworks
            except Exception, e:
                print e
                return {}

    def show_homework_detail_student(self, homework_id):
        with self.con:
            cur = self.con.cursor()
            sql = 'SELECT * from homework WHERE homework_id = %s'
            param = (homework_id,)
            cur.execute(sql, param)
            homework = cur.fetchone()
            now = datetime.now()
            if now >= homework[5]:
                dead = 1
            else:
                dead = 0
            return (homework, dead)

    def add_homework_student(self, user_id, class_id, url, method):
        with self.con:
            try:
                cur = self.con.cursor()
                if method == 'INSERT':
                    sql = 'INSERT INTO work (user, homework_id, score, url) VALUES (%s, %s, %s, %s)'
                    param = (user_id, class_id, -1, url)
                elif method == 'UPDATE':
                    sql = 'UPDATE work SET url = %s WHERE homework_id = %s AND user = %s'
                    param = (url, class_id, user_id)
                cur.execute(sql, param)
                return 1
            except Exception, e:
                print e
                return None

    def get_homework_student(self, user_id, class_id):
        with self.con:
            try:
                cur = self.con.cursor()
                sql = 'SELECT url from work WHERE user = %s AND homework_id = %s'
                param = (user_id, class_id)
                cur.execute(sql, param) 
                result = cur.fetchone()
                if not result:
                    return None
                else:
                    return result[0]
            except Exception, e:
                print e
                return None

if __name__ == '__main__':
    print myencode('hustcs')
